﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Data;

namespace VietKinsNutrition.Data
{
    public class ClsTblDm_SosanhnangluonDAL : ClsConnection
    {
        //
        public DataTable TblBuaantrongngayById(int KhoilopId)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand("TblDm_Buaantrongngay_ById", cn);
            DataTable table = new DataTable();
            ////commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.Parameters.Add(new OleDbParameter("@KhoilopId", KhoilopId));
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
            }

            catch (System.Exception ex)
            { MessageBox.Show("Error " + ex.Message); }

            return table;

        }
        public DataTable NCDDOTruong(string ProName, int KhoilopId)
        {
            ClsTblTruonghocDAL ClsTblTruonghocbll = new ClsTblTruonghocDAL();
            bool bVungmien = false;
            try
            {
                bVungmien = bool.Parse(ClsTblTruonghocbll.ReturnTblTRuong().Rows[0]["Vungmien"].ToString());
            }
            catch { }
            ProName = "SELECT  KhoilopId, iif(isnull(KCaloCd), 0, KCaloCd) as Calocd, iif(isnull(KCaloCtr), 0, KCaloCtr) as CaloCtr, "
              + " iif(isnull(ProtidCd), 0, ProtidCd) as PCd,  iif(isnull(ProtidCtr), 0, ProtidCtr) as PCtr, "
              + " iif(isnull(LipidCd),0,LipidCd) as LCd,iif(isnull( LipidCtr),0,LipidCtr) as LCtr, "
              + " iif(isnull( GlucidCd),0,GlucidCd) as GCd,iif(isnull( GlucidCtr),0,GlucidCtr) as GCtr "
              + " FROM         TblDm_Nhucaudinhduongotruong  WHERE KhoilopId = " + KhoilopId + " and Vungmien =" + (bVungmien ? "-1" : "0");

            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand(ProName, cn);
            DataTable table = new DataTable();
            //commad.CommandType = CommandType.StoredProcedure;
            try
            {
                //commad.Parameters.Add(new OleDbParameter("@KhoilopId", KhoilopId));
                //commad.Parameters.Add(new OleDbParameter("@Vungmien", bVungmien));
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
            }

            catch (System.Exception ex)
            { MessageBox.Show("Error " + ex.Message); }

            return table;

        }

        public DataTable GetTongsotre(string ProName, int KhoilopId, int NamhocId, DateTime Ngay)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand();
            if (cn.State != ConnectionState.Open)
                cn.Open();
            commad.Connection = cn;
            string str = "SELECT iif(isnull( TblBaoan.Tongsosuat),0,TblBaoan.Tongsosuat )AS Tongsosuat, iif(isnull( TblBaoan.Tongsotre),0,TblBaoan.Tongsotre) AS Tongsotre FROM TblBaoan WHERE (((TblBaoan.Ngay)=#" + Ngay.ToString("MM/dd/yyyy") + "#)  AND ((TblBaoan.LophocId)=" + KhoilopId + ")) ";
            commad.CommandText = str;//.Replace("@KhoilopId", KhoilopId.ToString()).Replace("@NamhocId", NamhocId.ToString()).Replace("@Ngay", Ngay.ToShortDateString());
            DataTable table = new DataTable();
            //commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
            }

            catch (System.Exception ex)
            { MessageBox.Show("Error " + ex.Message); }

            return table;

        }
        public DataTable GetTable(string ProName, int Option, int id, string Name)
        {

            OleDbConnection cn = ClsConnection.OleDBConnect();
            ProName = ProName.Replace("@Id", id.ToString());
            ProName = ProName.Replace("@Name", "'" + Name + "'");
            OleDbCommand commad = new OleDbCommand(ProName, cn);
            DataTable table = new DataTable();
            //commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
            }

            catch (System.Exception ex)
            { MessageBox.Show("Error " + ex.Message); }

            return table;

        }
        //
        public DataTable SP_Thucphamtheobua_SelectALL(DateTime Ngay, int KhoilopId)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand("SP_Thucphamtheobua_SelectALL", cn);
            DataTable table = new DataTable();
            //commad.CommandType = CommandType.StoredProcedure;

            try
            {
                commad.Parameters.Add(new OleDbParameter("@Ngay", Ngay));
                commad.Parameters.Add(new OleDbParameter("@KhoilopId", KhoilopId));
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
            }
            catch (System.Exception ex)
            { MessageBox.Show("Error " + ex.Message); }
            table.Columns.Add("Id", table.Columns["Thucpham_thucanId"].DataType);
            table.Columns.Add("BuaantrongngayId", table.Columns["Thucpham_thucanId"].DataType);
            table.Columns.Add("Khoilop", table.Columns["Thucpham_thucanId"].DataType);
            //  table.Columns.Add("ThuvienmonanId", table.Columns["Thucpham_thucanId"].DataType);
            table.Columns.Add("Ngay", table.Columns["ListTenthucan"].DataType);
            return table;
        }
        public DataTable GetTable(string TenBuaAn)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand(QueryString.TBLDM_BUAANTRONGNGAY_FINDNAMESAMEPRC, cn);
            DataTable table = new DataTable();
            //commad.CommandType = CommandType.StoredProcedure;

            try
            {
                commad.Parameters.Add("@Tenbuaan", OleDbType.VarWChar).Value = TenBuaAn;
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
            }
            catch (System.Exception ex)
            { MessageBox.Show("Error " + ex.Message); }
            return table;
        }
    }
}
